package org.su.generalmvc.jdbc.dao;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.su.generalmvc.jdbc.*;

import javax.sql.DataSource;
import java.sql.*;

/**
 * 数据库操作接口实现类
 * 
 * @author leimingbo
 * 
 */
public class Dao implements IDao {
	private volatile boolean pmdKnownBroken = false;
	private Connection conn = null;
	private int transLevel = 0;
	// 事务应用计数，支持嵌套事务
	private int transRefCount = 0;
	private int refCount = 0;
	private boolean transCommit = true;
	protected static final Log logger = LogFactory
	.getLog(Dao.class);
	// public Dao() {
	// this(0);
	// }

	// 请不要在应用代码中使用，框架专用
	public static Dao createDao(int level) {
		return new Dao(level);
	}

	private Dao(int level) {
		refCount++;
		try {
			conn = DaoFactory.getMasterConnection();
			if (transLevel == 0) {
				conn.setAutoCommit(true);
			} else {
				startTrans(level);
			}
		} catch (SQLException e) {
			close();
			throw new DBException(e);
		}
	}

	public Dao(DataSource dataSource) {
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	public Connection getConnection() {
		return conn;
	}

	/**
	 * 关闭事物
	 */
	@Override
	public void closeTransaction() {
		try {
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			throw new  DBException(e);
		}
	}

	/**
	 * 开启事物
	 */
	@Override
	public void openTransaction() {
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			throw new  DBException(e);
		}

	}

	public int getTransLevel() {
		return transLevel;
	}

	@Override
	public int delete(SqlBuilder sqlBuilder) {
		return execute(sqlBuilder);
	}

	@Override
	public int delete(String sql) {
		return execute(sql);
	}

	@Override
	public int delete(String sql, Object... params) {
		return execute(sql, params);
	}

	@Override
	public ResultSet find(Paging paging, SqlBuilder sqlBuilder) {
		return find(paging, sqlBuilder.getSql(), sqlBuilder.getParams());
	}

	@Override
	public ResultSet find(Paging paging, String sql, Object... params) {
		ResultSet rs = null;
		Statement statement = null;
		try {
			paging.setTotalResults(getMaxCount(paging, sql, params));

			StringBuffer sb = new StringBuffer(sql);
			sb.append(" limit ").append(paging.getFirstResult()).append(",")
					.append(paging.getPageResults());
			sql = sb.toString();

			if (params != null && params.length > 0) {
				PreparedStatement prepareStatement = null;
				prepareStatement = conn
						.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
								ResultSet.CONCUR_READ_ONLY);
				statement = prepareStatement;
				this.fillStatement(prepareStatement, params);
				rs = prepareStatement.executeQuery();
			} else {
				statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
						ResultSet.CONCUR_READ_ONLY);
				rs = statement.executeQuery(sql);
			}
		} catch (SQLException e) {
			closeStatement(statement, false);
			throw new DBException(e, sql, params);
		}
		return rs;
	}

	@Override
	public ResultSet findAll(SqlBuilder sqlBuilder) {
		return findAll(sqlBuilder.getSql(), sqlBuilder.getParams());
	}

	@Override
	public ResultSet findAll(String sql, Object... params) {
		ResultSet rs = null;
		Statement statement = null;
		try {
			if (params != null && params.length > 0) {
				PreparedStatement prepareStatement = conn
						.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
								ResultSet.CONCUR_READ_ONLY);
				statement = prepareStatement;
				this.fillStatement(prepareStatement, params);
				rs = prepareStatement.executeQuery();
			} else {
				statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
						ResultSet.CONCUR_READ_ONLY);
				rs = statement.executeQuery(sql);
			}
		} catch (SQLException e) {
			closeStatement(statement, false);
			throw new DBException(e, sql, params);
		}
		return rs;
	}

	@Override
	public int insert(SqlBuilder sqlBuilder) {
		return execute(sqlBuilder);
	}

	@Override
	public int insert(String sql, Object... params) {
		return execute(sql, params);
	}

	@Override
	public int update(SqlBuilder sqlBuilder) {
		return execute(sqlBuilder);
	}

	@Override
	public int update(String sql, Object... params) {
		return execute(sql, params);
	}

	@Override
	public void closeDao(boolean commit) {
		endTransAndClose(commit);
	}

	@Override
	public void close(ResultSet rs) {
		closeResultSet(rs);
	}

	public static void closeResultSet(ResultSet rs) {
		closeResultSet(rs, true);
	}

	public static void closeResultSet(ResultSet rs, boolean throwException) {
		if (rs != null) {

			try {
				Statement st = rs.getStatement();
				rs.close();
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				if (throwException) {
					throw new DBException("数据集关闭失败", e);
				}
			}
		}
	}

	@Override
	public PreparedStatement createPreparedStatement(String sql) {
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement = conn.prepareStatement(sql);
		} catch (SQLException e) {
			throw new DBException(e, sql);
		}
		return prepareStatement;
	}

	@Override
	public int execute(PreparedStatement statement, Object... objects) {
		int count = 0;
		try {
			if (objects != null && objects.length > 0) {
				this.fillStatement(statement, objects);
			}
			count = statement.executeUpdate();
		} catch (SQLException e) {
			throw new DBException(e, "STATEMENT操作", objects);
		}
		return count;
	}

	private int execute(String sql, Object... params) {
		PreparedStatement prepareStatement = null;
		Statement statement = null;
		if (sql == null) {
			return 0;
		}
		try {
			if (params == null || params.length == 0) {
				statement = conn.createStatement();
				return statement.executeUpdate(sql);
			} else {
				prepareStatement = conn.prepareStatement(sql);
				this.fillStatement(prepareStatement, params);
				return prepareStatement.executeUpdate();
			}

		} catch (SQLException e) {
			throw new DBException(e, sql, params);
		} finally {
			closeStatement(prepareStatement, false);
			closeStatement(statement, false);
		}
	}

	public void close(Statement statement) {
		closeStatement(statement, true);
	}

	public static void closeStatement(Statement statement,
			boolean throwExceptoin) {
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			if (throwExceptoin) {
				throw new DBException("statement关闭失败", e);
			}
		}

	}

	private int execute(SqlBuilder sqlBuilder) {
		Object[] obj = sqlBuilder.getParams();
		if (obj.length > 0) {
			return execute(sqlBuilder.getSql(), obj);
		} else {
			return execute(sqlBuilder.getSql());
		}
	}

	private void fillStatement(PreparedStatement stmt, Object... params) {
		if (params == null) {
			return;
		}
		ParameterMetaData pmd = null;
		try {
			if (!pmdKnownBroken) {
				pmd = stmt.getParameterMetaData();
				if (pmd.getParameterCount() < params.length) {
					throw new SQLException("Too many parameters: expected "
							+ pmd.getParameterCount() + ", was given "
							+ params.length);
				}
			}
			for (int i = 0; i < params.length; i++) {
				if (params[i] != null) {
					stmt.setObject(i + 1, params[i]);
				} else {
					int sqlType = Types.VARCHAR;
					if (!pmdKnownBroken) {
						try {
							sqlType = pmd.getParameterType(i + 1);
						} catch (SQLException e) {
							pmdKnownBroken = true;
						}
					}
					stmt.setNull(i + 1, sqlType);
				}
			}
		} catch (SQLException e) {
			throw new DBException(e, "STATME参数设置失败", params);
		}
	}

	private int getMaxCount(Paging paging, String sql, Object... params)
			throws SQLException {
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		int maxCount = 0;
		try {
			prepareStatement = conn.prepareStatement(getMaxCountSql(sql));
			if (params != null) {
				this.fillStatement(prepareStatement, params);
			}
			rs = prepareStatement.executeQuery();
			if (rs.next()) {
				maxCount = rs.getInt(1);
			}
			return maxCount;
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			closeResultSet(rs);
		}
	}

	private String getMaxCountSql(String sql) {
		StringBuilder sb = new StringBuilder("select count(*) ");
		int fromString = sql.indexOf("from", 0);
		int orderby = sql.indexOf("order by");
		int goupby = sql.indexOf("group by");
		if (orderby != -1) {
			sql = sql.substring(fromString, orderby);
		} else {
			sql = sql.substring(fromString);
		}
		if(goupby>0){
			sb.append(" from (select 1 ").append(sql).append(") as ssh");
		}else{
			sb.append(sql);
		}
		
		return sb.toString();
	}

	@Override
	public void rollback() {
		this.endTrans(false);
	}

	@Override
	public void forceClose() {
		refCount = 1;
		close();
	}

	@Override
	public void addRefCount() {
		this.refCount++;
	}

	@Override
	public void close() {
		if (--refCount <= 0 && conn != null) {
			try {
				if (transLevel != 0) {
					// 事务没有完成默认回滚
					transLevel = 0;
					transRefCount = 0;

					conn.rollback();
					conn.setAutoCommit(true);
					logger.error("db not call endTrans before close, now call rollback!!");
				}
			} catch (SQLException e) {
				// NOOP
				// throw new DBException("数据库事务执行失败", e);
				throw new DBException(e);
			} finally {
				try {
					conn.close();
				} catch (SQLException e) {
					// 不处理关闭数据库出错
					throw new DBException(e);
				} finally {
					// 清除本地线程
					DaoFactory.destroyDao(this);
				}

				conn = null;
			}
		}
	}

	@Override
	public void endTrans(boolean commit) {
		if (conn != null && transLevel != 0) {
			// 嵌套事务计数减1
			this.transRefCount--;
			if(!commit){
				this.transCommit = false;
			}
			if (this.transRefCount == 0) {
				boolean roolback = true;
				try {
					transLevel = 0;
					if (transCommit) {
						conn.commit();
						roolback = false;
						conn.setAutoCommit(true);
					}
				} catch (SQLException e) {
					throw new DBException(e);
				} finally {
					// commit = false 和 conn.commit 异常时都rollback
					if (roolback) {
						try {
							conn.rollback();
							conn.setAutoCommit(true);
						} catch (SQLException e) {
							// rollback 异常无法处理
							throw new DBException(e);
						}
					}
				}
			}
		}
	}

	@Override
	public void startTrans() {
		startTrans(Connection.TRANSACTION_REPEATABLE_READ);
	}

	@Override
	public void startTrans(int transLevel) {
		if (this.transLevel == 0) {
			// 第一次开始事务
			try {
				this.transCommit = true;
				conn.setAutoCommit(false);
				conn.setTransactionIsolation(transLevel);
			} catch (SQLException e) {
				throw new DBException(e);
			}
		} else if (transLevel > this.transLevel) {
			// 不能在嵌套事务中启动比初次事务级别高的事务
			throw new DBException("nest transnationt level is high."
					+ this.transLevel + "<" + transLevel);
		}

		this.transLevel = transLevel;
		this.transRefCount++;
	}

	public void endTransAndClose(boolean commit) {
		try {
			endTrans(commit);
		} finally {
			close();
		}
	}

	@Override
	public int queryInt(String sql, Object... params) {
		int result = 0;
		ResultSet rs = findAll(sql, params);

		try {
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new DBException(e, sql, params);
		} finally {
			close(rs);
		}

		return result;
	}

	@Override
	public long queryLong(String sql, Object... params) {
		long result = 0;
		ResultSet rs = findAll(sql, params);

		try {
			if (rs.next())
				result = rs.getLong(1);
		} catch (SQLException e) {
			throw new DBException(e, sql, params);
		} finally {
			close(rs);
		}

		return result;
	}
	
	@Override
	public float queryFloat(String sql, Object... params) {
		float result = 0;
		ResultSet rs = findAll(sql, params);

		try {
			if (rs.next())
				result = rs.getFloat(1);
		} catch (SQLException e) {
			throw new DBException(e, sql, params);
		} finally {
			close(rs);
		}

		return result;
	}

	@Override
	public short queryShort(String sql, Object... params) {
		short result = 0;
		ResultSet rs = findAll(sql, params);

		try {
			if (rs.next())
				result = rs.getShort(1);
		} catch (SQLException e) {
			throw new DBException(e, sql, params);
		} finally {
			close(rs);
		}

		return result;
	}

	@Override
	public String queryString(String sql, Object... params) {
		String result = null;
		ResultSet rs = findAll(sql, params);

		try {
			if (rs.next())
				result = rs.getString(1);
		} catch (SQLException e) {
			throw new DBException(e, sql, params);
		} finally {
			close(rs);
		}

		return result;
	}

	@Override
	public byte queryByte(String sql, Object... params) {
		byte result = 0;
		ResultSet rs = findAll(sql, params);

		try {
			if (rs.next()) {
				result = rs.getByte(1);
			}
		} catch (SQLException e) {
			throw new DBException(e, sql, params);
		} finally {
			close(rs);
		}

		return result;
	}
}
